﻿using MMS.Api.Helper;
using MMS.Model.FormatModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.WebPages;

namespace MMS.Api.Areas.Admin.Controllers
{
    public class SalesAnalysisController : BaseController
    {
        #region 1.0 加载 商品库存排名 数据
        #endregion

        #region 2.0 加载 商品销售排名 数据
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id">页码</param>
        /// <param name="id1">页容量</param>
        /// <param name="id2">排序条件</param>
        /// <param name="id3">升序或者降序</param>
        /// <returns></returns>
        public PageData<class_SaleItem> GetGoodsSaleWherePaged(string id, string id1, string id2, string id3)
        {
            int pageIndex = id.AsInt();
            int pageSize = id1.AsInt();
            int skip=(pageIndex-1)*pageSize;

            //1.查询数据集合
            string sql = "use MMS;with t as ( select SIGId ,COUNT(*) as Total from SaleItem group by SIGId ) select top " + pageSize + " GoodsName ,Total from ( select G.GName as GoodsName,Total,row_number() OVER (ORDER BY Total asc) AS [row_number] from t left join GoodsInfo as G on t.SIGId=G.GId ) as a where [row_number]>" + skip + " order by total " + id3;
            Model.FormatModel.PageData<class_SaleItem> pageData = new Model.FormatModel.PageData<class_SaleItem>();
             var list= OpeCur.BLLSession.GoodsInfo.SqlQuery<class_SaleItem>(sql).ToList();
             pageData.rows = list;
             string sql1 = "use MMS; select G.GName as GoodsName,total from (select SIGId,COUNT(*) as total from SaleItem group by SIGId ) as SI left join GoodsInfo as G on SI.SIGId=G.GId";
             var list1 = OpeCur.BLLSession.GoodsInfo.SqlQuery<class_SaleItem>(sql1).ToList();
             pageData.total = list1.Count();
             return pageData;
        }
        
        public List<class_SaleItem> GetAllGoodsSale()
        {
            string sql = "use MMS; select G.GName as GoodsName,total from (	select SIGId,COUNT(*) as total from SaleItem group by SIGId ) as SI left join GoodsInfo as G on SI.SIGId=G.GId";
            var list = OpeCur.BLLSession.GoodsInfo.SqlQuery<class_SaleItem>(sql).ToList();
            return list;
        }
        #endregion

        #region 3.0 加载 销售趋势分析 数据
        #endregion

        #region 4.0 加载 品类销售分析 数据
        public List<class_category> GetAllCategory()
       {
           string sql="use MMS;select C.CName as CategoryName,COUNT(*) as total from SaleItem as SI left join GoodsInfo as G on SI.SIGId=G.GId left join Category as C on C.CId=G.GCId group by C.CName ";
           var list = OpeCur.BLLSession.GoodsInfo.SqlQuery<class_category>(sql).ToList();
           return list;
       }
        #endregion

       #region 5.0 加载 会员推广与销售统计 数据
       /// <summary>
        /// 
        /// </summary>
       /// <param name="id">startdate</param>
        /// <param name="id1">enddate</param>
        /// <returns></returns>
       public List<class_PromotionAndSales> GetPromotionAndSales(string id, string id1)
       {
           if (id.Length == 14)
           {
               DateTime startdate = DateTime.ParseExact(id, "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
               id = startdate.ToString();
               DateTime enddate = DateTime.ParseExact(id1, "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
               id1 = enddate.ToString();
           }
           string sql = "use MMS;select SUM(SIActualPay) as Total ,M.MName as MemberName from SaleItem as SI left join MemberInfo as M on SI.SIMId=M.MId where SI.SITime between '" + id + "' and '" + id1 + "' group by M.MName";
           var list = OpeCur.BLLSession.GoodsInfo.SqlQuery<class_PromotionAndSales>(sql).ToList();
           return list;
       }
        #endregion

        #region 6.0 加载 会员消费分阶人数统计表 数据
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id">日期</param>
        /// <returns></returns>
       public List<class_RankOrder> GetRankOrder(string id)
       {
           string[] date=id.Split('-');
           string sql = "use MMS;select SUM(TActualPay)/COUNT(*) as PerCustomerTransaction ,datepart(HOUR,TTime) as saleTime from TradeInfo where datepart(YEAR,TTime)=" + date[0] + " and datepart(MONTH,TTime)=" + date[1] + " and datepart(DAY,TTime)=" + date[2] + " group by datepart(HOUR,TTime)";
           var list = OpeCur.BLLSession.GoodsInfo.SqlQuery<class_RankOrder>(sql).ToList();
           return list;
       }
        #endregion

        #region 7.0 加载 会员月度购物频次统计 数据
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id">年份</param>
        /// <param name="id1">地区</param>
        /// <returns></returns>
       public List<class_ShoppingFrequency> GetShoppingFrequency(int id, string id1)
       {
           string sql = "use MMS;select COUNT(*) as Total,datepart(MONTH,TTime) as PartTime from TradeInfo where datepart(YEAR,TTime) =" + id + " and TAddress='" + id1 + "' group by datepart(MONTH,TTime)";
           var list = OpeCur.BLLSession.GoodsInfo.SqlQuery<class_ShoppingFrequency>(sql).ToList();
           return list;
       }
        #endregion
    }
}
